/**
 * 
 */
package com.anyuan.wx.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.http.HttpServletRequest;

import com.anyuan.wx.pojo.UserLocation;


/**
 * @author liuyanbin
 * @date 2014-3-14
 */
public class MySQLUtil {
	
	private Connection getConn(HttpServletRequest request)
	{
		Connection conn = null;
		//从request 请求中去除IP,端口，用户名，密码
		String host = request.getHeader("BAE_ENV_ADDR_SQL_IP");
		String port = request.getHeader("BAE_ENV_ADDR_SQL_PORT");
		String username = request.getHeader("BAE_ENV_AK");
		String password = request.getHeader("BAE_ENV_SK");
		
		String dbName = "NggNAsekjwXpENEbkUsY";
		
		String url = String.format("jdbc:mysql://%s:%s:%s",host,port,dbName);
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(Exception e)
		{
			e.printStackTrace();
		}		
		return conn;
	}
	
	public static void saveUserLocation(HttpServletRequest request,String openId,String lng,String lat,String bd09_lng,
			String bd09_lat)
	{
		String sql = "insert into user_location(open_id,lng,lat,bd09_lng,bd09_lat) values(?,?,?,?,?)";
		try{
			Connection conn = new MySQLUtil().getConn(request);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, openId);
			ps.setString(2, lng);
			ps.setString(3, lat);
			ps.setString(4, bd09_lng);
			ps.setString(5, bd09_lat);
			ps.executeUpdate();
			
			ps.close();
			conn.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}
	
	public static UserLocation getLastLocation(HttpServletRequest request,String openId) 
	{
		UserLocation userLocation = null;
		String sql = "select open_id,lng,lat,bd09_lng,bd09_lat from user_location where  open_id = ? order by id desc limit 0,1";
		
		try{
			
			Connection conn = new MySQLUtil().getConn(request);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, openId);
			ResultSet rs = ps.executeQuery();
			if(rs.next())
			{
				userLocation = new UserLocation();
				userLocation.setOpenId(rs.getString("open_id"));
				userLocation.setLng(rs.getString("lng"));
				userLocation.setLat(rs.getString("lat"));
				userLocation.setBd09Lat("bd09_lat");
				userLocation.setBd09Lng("bd09_lng");
			}
			
			rs.close();
			ps.close();
			conn.close();
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return userLocation;
	}

}
